#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_dws.wf_dws_cus_day_cnt
with temp as(select
    id,
    substr(create_date_time,1,10) as dt,
    deleted,
    customer_id,
    origin_type,
    origin_channel,
    creator,
    itcast_school_id,
    itcast_subject_id,
    customer_relationship_id,
    clue_state,
    cus_id,
    area,
    emp_id,
    tdepart_id,
    dep_id,
    dep_name as center_name,
    school_id,
    school_name,
    subject_id,
    subject_name,
    row_number() over (partition by id) as id_rn,
    row_number() over (partition by id,origin_channel) as origin_rn,
    row_number() over (partition by id,clue_state) as no_rn,
    row_number() over (partition by id,subject_id) as sub_rn,
    row_number() over (partition by id,dep_name) as center_rn,
    row_number() over (partition by id,school_id) as school_rn,
    row_number() over (partition by id,area) as area_rn
from hive.edu_dwb.wf_dwb_customer_relationship_wide)
-- 每天线上线下以及新老学员的意向用户数
select
    dt,
    origin_type,
    origin_channel,
    clue_state,
    area,
    center_name,
    school_id,school_name,
    subject_id,subject_name,
    case when grouping(subject_id)=0 then 'sub'
        when grouping(center_name)=0 then 'center'
        when grouping(school_id)=0 then 'school'
        when grouping(area)=0 then 'area'
        when grouping(origin_channel)=0 then 'origin'
        when grouping(area)=0 then 'area'
        else 'all'
    end as group_type,
    case when grouping(subject_id)=0 then count(if(subject_id is not null and sub_rn=1,id,null))
        when grouping(center_name)=0 then count(if(center_name is not null and center_rn=1,id,null))
        when grouping(school_id)=0 then count(if(school_id is not null and school_rn=1,id,null))
        when grouping(area)=0 then count(if(area is not null and area_rn=1,id,null))
        when grouping(origin_channel)=0 then count(if(origin_channel is not null and origin_rn=1,id,null))
        when grouping(dt)=0 then count(if(dt is not null and id_rn=1,id,null))
    else 0 end as rel_cnt
from temp
group by
grouping sets(
  (dt,origin_type,clue_state),
  (dt,origin_type,clue_state,origin_channel),
  (dt,origin_type,clue_state,school_id,school_name),
  (dt,origin_type,clue_state,subject_id,subject_name),
  (dt,origin_type,clue_state,area),
  (dt,origin_type,clue_state,center_name)
);


insert into hive.edu_dws.wf_dws_clu_hour_cnt
with tmp as(

                     select id,
                            customer_relationship_id,
                            substr(create_date_time, 1, 10) as dt,
                            substr(create_date_time, 1, 13) as hr,
                            clue_state,
                            rel_id,
                            deleted,
                            origin_type,
                            customer_relationship_first_id,
                            appeal_status,
                            row_number() over (partition by id) as id_rn
                     from hive.edu_dwb.wf_dwb_customer_clue_wide

    ),
a as (select
    dt,
    origin_type,clue_state,
    count(if(id_rn=1 and id is not null ,id,null)) as clu_day_cnt
from tmp where appeal_status = 0 or appeal_status = 2
group by dt,origin_type,clue_state),
b as (select
    hr,
    origin_type,clue_state,
    count(if(id_rn=1 and id is not null ,id,null)) as eff_hour_cnt
from tmp where appeal_status = 0 or appeal_status = 2
group by hr,origin_type,clue_state),
c as (select
    hr,
    origin_type,clue_state,
    count(if(id_rn=1 and id is not null ,id,null)) as clu_hour_cnt
from tmp
group by hr,origin_type,clue_state),
clu_temp as (
select dt,
       b.hr,
       a.origin_type,
       a.clue_state,
       clu_day_cnt,
       eff_hour_cnt,
       clu_hour_cnt
from a
left join b on a.dt = substr(b.hr,1,10)
left join c on b.hr=c.hr
)
select
    dt,
    hr,
    clue_state,
    origin_type,
    clu_day_cnt,
    clu_hour_cnt,
    eff_hour_cnt,
    if(clu_hour_cnt=0,0,eff_hour_cnt*1.00/clu_hour_cnt) as clu_hour_rate
from clu_temp;"